{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "b8f25c9d",
   "metadata": {},
   "source": [
    "# Task\n",
    "\n",
    "This notebook is used to produce Table 2 in the paper. This Table computes a price decomposition of the growth in the GDP share of health expenditures. We use data from Eurostat, the BLS and the OECD for this task. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "ff0cb125",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np \n",
    "from matplotlib import pyplot as plt\n",
    "import pandas as pd\n",
    "import csv\n",
    "from pandas import read_excel\n",
    "from scipy import stats\n",
    "from scipy.stats import spearmanr"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "984edd8c",
   "metadata": {},
   "source": [
    "A month to year data conversion function. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "9396879c",
   "metadata": {},
   "outputs": [],
   "source": [
    "def m2y(monthly):\n",
    "    size   = monthly.shape\n",
    "    Tm     = size[0]\n",
    "    N      = size[1]\n",
    "    Ty     = int(np.round(Tm/12))\n",
    "    annual = np.zeros((Ty,N))\n",
    "    for kk in range(N):\n",
    "        for jj in range(Ty):\n",
    "            annual[jj,kk] = np.mean(monthly[jj*12:(jj+1)*12-1,kk])\n",
    "    return annual"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f1289d1d",
   "metadata": {},
   "source": [
    "A number of parameters that need to be set for the computation. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "8a2aad0d",
   "metadata": {},
   "outputs": [],
   "source": [
    "N       = 8 #8 countries\n",
    "year    = np.arange(1996, 2020, 1) # sample data\n",
    "T       = year.size\n",
    "xsmooth = 5 # length for the MA"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bd5569bb",
   "metadata": {},
   "source": [
    "# Step 1: Load price series and compute growth rates. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "197d4ac0",
   "metadata": {},
   "source": [
    "## US data\n",
    "\n",
    "We load the BLS price series. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "f8e15e51",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/flangot/opt/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:4: FutureWarning: Your version of xlrd is 1.2.0. In xlrd >= 2.0, only the xls format is supported. As a result, the openpyxl engine will be used if it is installed and the engine argument is not specified. Install openpyxl instead.\n",
      "  after removing the cwd from sys.path.\n",
      "/Users/flangot/opt/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:6: FutureWarning: Your version of xlrd is 1.2.0. In xlrd >= 2.0, only the xls format is supported. As a result, the openpyxl engine will be used if it is installed and the engine argument is not specified. Install openpyxl instead.\n",
      "  \n"
     ]
    }
   ],
   "source": [
    "file_name = '../data_sources/prices/DataPriceUS.xlsx'\n",
    "\n",
    "my_sheet  = 'CUSR0000SA0'    # sheet name\n",
    "xx_us0 = read_excel(file_name, sheet_name = my_sheet)\n",
    "my_sheet  = 'CUSR0000SAM'    # sheet name\n",
    "xx_us1 = read_excel(file_name, sheet_name = my_sheet)\n",
    "\n",
    "xx_us = np.array([xx_us0.loc[11:378,'Unnamed: 3'], xx_us1.loc[11:378,'Unnamed: 3']])\n",
    "xx_us = xx_us.T"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9ee23cd2",
   "metadata": {},
   "source": [
    "We select years, transform monthly to yearly data and then compute a moving average smoothing. We then create our index. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "96afdab4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# select 1996:m1 to 2019:m12\n",
    "xx_us_m =xx_us[72:368-8,:]\n",
    "# Annualized data\n",
    "cpiy_us = m2y(xx_us_m)\n",
    "# Moving average\n",
    "cpima_us = np.zeros((24,2))\n",
    "for ii in range(2):\n",
    "    d = pd.Series(cpiy_us[:,ii])\n",
    "    cpima_us[:,ii] = d.rolling(xsmooth,min_periods=1,center=True).mean()\n",
    "# Index\n",
    "cpi_us = np.zeros((24,2))\n",
    "cpi_us[:,0] = cpima_us[:,0]/cpima_us[0,0]\n",
    "cpi_us[:,1] = cpima_us[:,1]/cpima_us[0,1]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4b504207",
   "metadata": {},
   "source": [
    "We compute growth rates. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "9fb9cb35",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "mean 1996-2019: delta(log(cpi))\n",
      "     cpi    cpihealth\n",
      "[0.01987873 0.03251502]\n",
      "phat = mean(cpi health) - mean(cpi)\n",
      "0.012636284479829951\n"
     ]
    }
   ],
   "source": [
    "# Growth rates\n",
    "dlcpi_us=np.log(cpi_us[1:-1,:])-np.log(cpi_us[0:-2,:])\n",
    "avcpi = np.mean(dlcpi_us,0)\n",
    "phat_us = np.mean(dlcpi_us[:,1])- np.mean(dlcpi_us[:,0])\n",
    "print('mean 1996-2019: delta(log(cpi))')\n",
    "print('     cpi    cpihealth')\n",
    "print(avcpi)\n",
    "print('phat = mean(cpi health) - mean(cpi)')\n",
    "print(phat_us)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e6490cb2",
   "metadata": {},
   "source": [
    "We will repeat these steps for each country. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4f1eb18d",
   "metadata": {},
   "source": [
    "## DE data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "39b8b573",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/flangot/opt/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:3: FutureWarning: Your version of xlrd is 1.2.0. In xlrd >= 2.0, only the xls format is supported. As a result, the openpyxl engine will be used if it is installed and the engine argument is not specified. Install openpyxl instead.\n",
      "  This is separate from the ipykernel package so we can avoid doing imports until\n",
      "/Users/flangot/opt/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:7: FutureWarning: Your version of xlrd is 1.2.0. In xlrd >= 2.0, only the xls format is supported. As a result, the openpyxl engine will be used if it is installed and the engine argument is not specified. Install openpyxl instead.\n",
      "  import sys\n"
     ]
    }
   ],
   "source": [
    "file_name = '../data_sources/prices/CPI_de.xlsx'\n",
    "my_sheet  = 'FRED Graph'    # sheet name\n",
    "xx_de0 = read_excel(file_name, sheet_name = my_sheet)\n",
    "\n",
    "file_name = '../agghealth_replicate/data_sources/prices/CPIH_de.xlsx'\n",
    "my_sheet  = 'FRED Graph'    # sheet name\n",
    "xx_de1 = read_excel(file_name, sheet_name = my_sheet)\n",
    "\n",
    "# select 1996:m1 to 2019:m12\n",
    "xx_de = np.array([xx_de0.loc[10:305-8,'Unnamed: 1'], xx_de1.loc[10:305-8,'Unnamed: 1']])\n",
    "xx_de = xx_de.T"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9b67875a",
   "metadata": {},
   "source": [
    "## DK data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "a40bf209",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/flangot/opt/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:3: FutureWarning: Your version of xlrd is 1.2.0. In xlrd >= 2.0, only the xls format is supported. As a result, the openpyxl engine will be used if it is installed and the engine argument is not specified. Install openpyxl instead.\n",
      "  This is separate from the ipykernel package so we can avoid doing imports until\n",
      "/Users/flangot/opt/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:7: FutureWarning: Your version of xlrd is 1.2.0. In xlrd >= 2.0, only the xls format is supported. As a result, the openpyxl engine will be used if it is installed and the engine argument is not specified. Install openpyxl instead.\n",
      "  import sys\n"
     ]
    }
   ],
   "source": [
    "file_name = '../agghealth_replicate/data_sources/prices/CPI_dk.xlsx'\n",
    "my_sheet  = 'FRED Graph'    # sheet name\n",
    "xx_dk0 = read_excel(file_name, sheet_name = my_sheet)\n",
    "\n",
    "file_name = '../agghealth_replicate/data_sources/prices/CPIH_dk.xlsx'\n",
    "my_sheet  = 'FRED Graph'    # sheet name\n",
    "xx_dk1 = read_excel(file_name, sheet_name = my_sheet)\n",
    "\n",
    "# select 1996:m1 to 2019:m12\n",
    "xx_dk = np.array([xx_dk0.loc[10:305-8,'Unnamed: 1'], xx_dk1.loc[10:305-8,'Unnamed: 1']])\n",
    "xx_dk = xx_dk.T"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "36a341e3",
   "metadata": {},
   "source": [
    "## FR data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "f525133a",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/flangot/opt/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:3: FutureWarning: Your version of xlrd is 1.2.0. In xlrd >= 2.0, only the xls format is supported. As a result, the openpyxl engine will be used if it is installed and the engine argument is not specified. Install openpyxl instead.\n",
      "  This is separate from the ipykernel package so we can avoid doing imports until\n",
      "/Users/flangot/opt/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:7: FutureWarning: Your version of xlrd is 1.2.0. In xlrd >= 2.0, only the xls format is supported. As a result, the openpyxl engine will be used if it is installed and the engine argument is not specified. Install openpyxl instead.\n",
      "  import sys\n"
     ]
    }
   ],
   "source": [
    "file_name = '../data_sources/prices/CPI_fr.xlsx'\n",
    "my_sheet  = 'FRED Graph'    # sheet name\n",
    "xx_fr0 = read_excel(file_name, sheet_name = my_sheet)\n",
    "\n",
    "file_name = '../agghealth_replicate/data_sources/prices/CPIH_fr.xlsx'\n",
    "my_sheet  = 'FRED Graph'    # sheet name\n",
    "xx_fr1 = read_excel(file_name, sheet_name = my_sheet)\n",
    "\n",
    "# select 1996:m1 to 2019:m12\n",
    "xx_fr = np.array([xx_fr0.loc[10:305-8,'Unnamed: 1'], xx_fr1.loc[10:305-8,'Unnamed: 1']])\n",
    "xx_fr = xx_fr.T"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "982c9b28",
   "metadata": {},
   "source": [
    "## IT data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "c0deb941",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/flangot/opt/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:3: FutureWarning: Your version of xlrd is 1.2.0. In xlrd >= 2.0, only the xls format is supported. As a result, the openpyxl engine will be used if it is installed and the engine argument is not specified. Install openpyxl instead.\n",
      "  This is separate from the ipykernel package so we can avoid doing imports until\n",
      "/Users/flangot/opt/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:7: FutureWarning: Your version of xlrd is 1.2.0. In xlrd >= 2.0, only the xls format is supported. As a result, the openpyxl engine will be used if it is installed and the engine argument is not specified. Install openpyxl instead.\n",
      "  import sys\n"
     ]
    }
   ],
   "source": [
    "file_name = '../data_sources/prices/CPI_it.xlsx'\n",
    "my_sheet  = 'FRED Graph'    # sheet name\n",
    "xx_it0 = read_excel(file_name, sheet_name = my_sheet)\n",
    "\n",
    "file_name = '../data_sources/prices/CPIH_it.xlsx'\n",
    "my_sheet  = 'FRED Graph'    # sheet name\n",
    "xx_it1 = read_excel(file_name, sheet_name = my_sheet)\n",
    "\n",
    "# select 1996:m1 to 2019:m12\n",
    "xx_it = np.array([xx_it0.loc[10:305-8,'Unnamed: 1'], xx_it1.loc[10:305-8,'Unnamed: 1']])\n",
    "xx_it = xx_it.T"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1ebf1aad",
   "metadata": {},
   "source": [
    "## NL data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "49ecb35d",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/flangot/opt/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:3: FutureWarning: Your version of xlrd is 1.2.0. In xlrd >= 2.0, only the xls format is supported. As a result, the openpyxl engine will be used if it is installed and the engine argument is not specified. Install openpyxl instead.\n",
      "  This is separate from the ipykernel package so we can avoid doing imports until\n",
      "/Users/flangot/opt/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:7: FutureWarning: Your version of xlrd is 1.2.0. In xlrd >= 2.0, only the xls format is supported. As a result, the openpyxl engine will be used if it is installed and the engine argument is not specified. Install openpyxl instead.\n",
      "  import sys\n"
     ]
    }
   ],
   "source": [
    "file_name = '../data_sources/prices/CPI_nl.xlsx'\n",
    "my_sheet  = 'FRED Graph'    # sheet name\n",
    "xx_nl0 = read_excel(file_name, sheet_name = my_sheet)\n",
    "\n",
    "file_name = '../data_sources/prices/CPIH_nl.xlsx'\n",
    "my_sheet  = 'FRED Graph'    # sheet name\n",
    "xx_nl1 = read_excel(file_name, sheet_name = my_sheet)\n",
    "\n",
    "# select 1996:m1 to 2019:m12\n",
    "xx_nl = np.array([xx_nl0.loc[10:305-8,'Unnamed: 1'], xx_nl1.loc[10:305-8,'Unnamed: 1']])\n",
    "xx_nl = xx_nl.T"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e32b407a",
   "metadata": {},
   "source": [
    "## SE data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "7d8c8255",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/flangot/opt/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:3: FutureWarning: Your version of xlrd is 1.2.0. In xlrd >= 2.0, only the xls format is supported. As a result, the openpyxl engine will be used if it is installed and the engine argument is not specified. Install openpyxl instead.\n",
      "  This is separate from the ipykernel package so we can avoid doing imports until\n",
      "/Users/flangot/opt/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:7: FutureWarning: Your version of xlrd is 1.2.0. In xlrd >= 2.0, only the xls format is supported. As a result, the openpyxl engine will be used if it is installed and the engine argument is not specified. Install openpyxl instead.\n",
      "  import sys\n"
     ]
    }
   ],
   "source": [
    "file_name = '../data_sources/prices/CPI_se.xlsx'\n",
    "my_sheet  = 'FRED Graph'    # sheet name\n",
    "xx_se0 = read_excel(file_name, sheet_name = my_sheet)\n",
    "\n",
    "file_name = '../data_sources/prices/CPIH_se.xlsx'\n",
    "my_sheet  = 'FRED Graph'    # sheet name\n",
    "xx_se1 = read_excel(file_name, sheet_name = my_sheet)\n",
    "\n",
    "# select 1996:m1 to 2019:m12\n",
    "xx_se = np.array([xx_se0.loc[10:305-8,'Unnamed: 1'], xx_se1.loc[10:305-8,'Unnamed: 1']])\n",
    "xx_se = xx_se.T"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0c5c9b87",
   "metadata": {},
   "source": [
    "## SP data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "083c1a76",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/flangot/opt/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:3: FutureWarning: Your version of xlrd is 1.2.0. In xlrd >= 2.0, only the xls format is supported. As a result, the openpyxl engine will be used if it is installed and the engine argument is not specified. Install openpyxl instead.\n",
      "  This is separate from the ipykernel package so we can avoid doing imports until\n",
      "/Users/flangot/opt/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:7: FutureWarning: Your version of xlrd is 1.2.0. In xlrd >= 2.0, only the xls format is supported. As a result, the openpyxl engine will be used if it is installed and the engine argument is not specified. Install openpyxl instead.\n",
      "  import sys\n"
     ]
    }
   ],
   "source": [
    "file_name = '../data_sources/prices/CPI_sp.xlsx'\n",
    "my_sheet  = 'FRED Graph'    # sheet name\n",
    "xx_sp0 = read_excel(file_name, sheet_name = my_sheet)\n",
    "\n",
    "file_name = '../data_sources/prices/CPIH_sp.xlsx'\n",
    "my_sheet  = 'FRED Graph'    # sheet name\n",
    "xx_sp1 = read_excel(file_name, sheet_name = my_sheet)\n",
    "\n",
    "# select 1996:m1 to 2019:m12\n",
    "xx_sp = np.array([xx_sp0.loc[10:305-8,'Unnamed: 1'], xx_sp1.loc[10:305-8,'Unnamed: 1']])\n",
    "xx_sp = xx_sp.T"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "91b9b96c",
   "metadata": {},
   "source": [
    "# Step 2: Regrouping all data\n",
    "\n",
    "We collect all data together. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "3a5a3bce",
   "metadata": {},
   "outputs": [],
   "source": [
    "xx_data = np.zeros((288,N))\n",
    "xx_data = np.array([xx_de[:,0], xx_dk[:,0], xx_fr[:,0], xx_it[:,0], xx_nl[:,0], xx_se[:,0], xx_sp[:,0], xx_us_m[:,0]]).T\n",
    "xx_data = xx_data.astype(float)\n",
    "\n",
    "xxh_data = np.zeros((288,N))\n",
    "xxh_data = np.array([xx_de[:,1], xx_dk[:,1], xx_fr[:,1], xx_it[:,1], xx_nl[:,1], xx_se[:,1], xx_sp[:,1], xx_us_m[:,1]]).T\n",
    "xxh_data = xxh_data.astype(float)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f0d4cdfa",
   "metadata": {},
   "source": [
    "# Step 3: inflation rates across countries"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0a70f849",
   "metadata": {},
   "source": [
    "Create population weights for each countries (OECD data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "09488696",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Weights for European countries\n",
    "weight_co = np.array([.2827, .0197, .2311, .2117, .0588, .0339, .1617])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "54bec65d",
   "metadata": {},
   "source": [
    "Select the relevant sub-sample"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "cbe1632f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Select a subsample: 1996-2007\n",
    "stops = np.where(year==2007)\n",
    "stopa = stops[0]\n",
    "stop  = stopa[0]\n",
    "\n",
    "size_smpl = xx_data.shape\n",
    "size_s    = int(size_smpl[0]/12)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4bc5bba6",
   "metadata": {},
   "source": [
    "Annualize the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "4d88d9b7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Annualized data\n",
    "xxy_data  = m2y(xx_data)\n",
    "xxhy_data = m2y(xxh_data)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5d9b1d53",
   "metadata": {},
   "source": [
    "Compute a moving average"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "b4e12b26",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Mouving average\n",
    "xxyma_data = np.zeros((size_s,N))\n",
    "for ii in range(8):\n",
    "    d = pd.Series(xxy_data[:,ii])\n",
    "    xxyma_data[:,ii] = d.rolling(xsmooth,min_periods=1,center=True).mean()\n",
    "    xxyma_data[:,ii] = xxyma_data[:,ii]/xxyma_data[0,ii]\n",
    "\n",
    "xxhyma_data = np.zeros((size_s ,N))\n",
    "for ii in range(8):\n",
    "    d = pd.Series(xxhy_data[:,ii])\n",
    "    xxhyma_data[:,ii] = d.rolling(xsmooth,min_periods=1,center=True).mean()\n",
    "    xxhyma_data[:,ii] = xxhyma_data[:,ii]/xxhyma_data[0,ii]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "f1bfe0a8",
   "metadata": {},
   "outputs": [],
   "source": [
    "dlcpi  = np.log(xxyma_data[1:stop+1,:])-np.log(xxyma_data[0:stop,:])\n",
    "dlcpih = np.log(xxhyma_data[1:stop+1,:])-np.log(xxhyma_data[0:stop,:])\n",
    "\n",
    "cpih_avgrowth = 100*np.mean(dlcpih[0:stop,:],0)\n",
    "cpi_avgrowth  = 100*np.mean(dlcpi[0:stop,:],0)\n",
    "cpih_avgrowth_eu = np.sum(weight_co*cpih_avgrowth[0:7])\n",
    "cpi_avgrowth_eu  = np.sum(weight_co*cpi_avgrowth[0:7])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "244e240e",
   "metadata": {},
   "source": [
    "This is the data on growth rates. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "be4d6daf",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "average annual growth rate 1996-2007\n",
      "DE  DK  FR  IT  NL  SE  SP  US AvEU\n",
      "cpi_health   \n",
      "[array([2.58, 1.29, 1.79, 2.41, 3.14, 2.51, 1.48, 3.62]), 2.19]\n",
      "cpi          \n",
      "[array([1.34, 1.78, 1.54, 2.06, 2.12, 1.43, 2.66, 2.33]), 1.81]\n",
      "cpi_h - cpi  \n",
      "[array([ 1.24, -0.49,  0.25,  0.35,  1.01,  1.08, -1.18,  1.29]), 0.38]\n"
     ]
    }
   ],
   "source": [
    "print('average annual growth rate 1996-2007')\n",
    "print('DE  DK  FR  IT  NL  SE  SP  US AvEU')\n",
    "print('cpi_health   ')\n",
    "print([np.around(cpih_avgrowth,2), np.around(cpih_avgrowth_eu,2)])\n",
    "print('cpi          ')\n",
    "print([np.around(cpi_avgrowth,2), np.around(cpi_avgrowth_eu,2)])\n",
    "print('cpi_h - cpi  ')\n",
    "print([np.around(cpih_avgrowth-cpi_avgrowth,2), np.around(cpih_avgrowth_eu-cpi_avgrowth_eu,2)])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7990eb6b",
   "metadata": {},
   "source": [
    "For comparison with previous calculations in Matlab: \n",
    "1.08   -0.50   0.25   0.274   0.92   0.92   -1.31   1.31 0.28"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9e243d8b",
   "metadata": {},
   "source": [
    "# Step 4: GDP shares of health expenditures"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "56ef1b1c",
   "metadata": {},
   "source": [
    "We use the GDP shares of health expenditures from OECD. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "d5ed01cc",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/flangot/opt/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:3: FutureWarning: Your version of xlrd is 1.2.0. In xlrd >= 2.0, only the xls format is supported. As a result, the openpyxl engine will be used if it is installed and the engine argument is not specified. Install openpyxl instead.\n",
      "  This is separate from the ipykernel package so we can avoid doing imports until\n"
     ]
    }
   ],
   "source": [
    "file_name = '../data_sources/prices/data_regFL.xlsx'\n",
    "my_sheet  = 'Sheet1'    # sheet name\n",
    "data_reg = read_excel(file_name, sheet_name = my_sheet)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "7ac0c4d2",
   "metadata": {},
   "outputs": [],
   "source": [
    "Country_lab = ['Germany', 'Denmark', 'France', 'Italy', 'Netherlands', 'Sweden', 'Spain', 'United States']\n",
    "\n",
    "dd = np.zeros((24,N))\n",
    "for ii in range(8):\n",
    "    dei  = np.where((data_reg.loc[:,'country']==Country_lab[ii]) & (data_reg.loc[:,'year']>1995))\n",
    "    deia = dei[0]\n",
    "    deib = deia\n",
    "    dd[:,ii] = deib.tolist()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "e642d515",
   "metadata": {},
   "outputs": [],
   "source": [
    "pmc = data_reg.loc[:,'tothlthcap']  \n",
    "yrc = data_reg.loc[:,'gdp15ncucap']\n",
    "yc  = data_reg.loc[:,'gdpcap']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "e4652858",
   "metadata": {},
   "outputs": [],
   "source": [
    "datapm = np.zeros((24,N))\n",
    "datayr = np.zeros((24,N))\n",
    "datay  = np.zeros((24,N))\n",
    "\n",
    "for ii in range(8):\n",
    "    d0 = pd.Series(pmc[dd[:,ii].tolist()])\n",
    "    datapm[:,ii] = d0.rolling(xsmooth,min_periods=1,center=True).mean()\n",
    "    d1 = pd.Series(yrc[dd[:,ii].tolist()])\n",
    "    datayr[:,ii] = d1.rolling(xsmooth,min_periods=1,center=True).mean()\n",
    "    d2 = pd.Series(yc[dd[:,ii].tolist()])\n",
    "    datay[:,ii] = d2.rolling(xsmooth,min_periods=1,center=True).mean()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "31db4d77",
   "metadata": {},
   "source": [
    "Compute the deflator"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "a6de6b75",
   "metadata": {},
   "outputs": [],
   "source": [
    "share   = datapm/datay\n",
    "gdpdef0 = datay/datayr # GDP deflator 2015\n",
    "gdpdef  = gdpdef0/np.tile(gdpdef0[0,:],(T,1)) # GDP deflator 1996\n",
    "\n",
    "datayreal96  = datay/gdpdef # GDP real, base 1996%%%%%%%%%%%\n",
    "datapmreal96 = datapm/xxhyma_data # pm real, base 1996\n",
    "share_real   = datapmreal96/datayreal96\n",
    "\n",
    "shat = (np.mean(share,0)-np.mean(share_real,0))/np.mean(share_real,0)\n",
    "shateu = (np.sum(weight_co*100*np.mean(share[:,0:7],0)) - np.sum(weight_co*100*np.mean(share_real[:,0:7],0)))/np.sum(weight_co*100*np.mean(share_real[:,0:7],0))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3f3b2f96",
   "metadata": {},
   "source": [
    "This is the data for the Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "3045fd4e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "average 1996-2007 (in %)\n",
      "DE  DK  FR  IT  NL  SE  SP  US AvEU\n",
      "pm/y   \n",
      "[array([10.63,  9.34, 10.62,  8.26,  9.25,  9.03,  8.12, 15.04]), 9.55]\n",
      "m/y    \n",
      "[array([ 9.37, 10.03, 10.43,  7.82,  8.13,  8.25,  9.24, 12.42]), 9.16]\n",
      "shate  \n",
      "[ 13.42  -6.85   1.86   5.57  13.69   9.48 -12.1   21.09] 4.26\n"
     ]
    }
   ],
   "source": [
    "print('average 1996-2007 (in %)')\n",
    "print('DE  DK  FR  IT  NL  SE  SP  US AvEU')\n",
    "print('pm/y   ')\n",
    "print([ np.around(100*np.mean(share,0),2), np.around(np.sum(weight_co*100*np.mean(share[:,0:7],0)),2) ] )\n",
    "print('m/y    ')\n",
    "print([ np.around(100*np.mean(share_real,0),2), np.around(np.sum(weight_co*100*np.mean(share_real[:,0:7],0)),2) ] )\n",
    "print('share  ')\n",
    "print( np.around(100*shat,2), np.around(100*shateu,2))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "854baf91",
   "metadata": {},
   "source": [
    "For comparison with previous results using  Matlab\n",
    "13.39   -6.81   1.83   5.50   13.68  9.46  -12.14  21.10"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  },
  "vscode": {
   "interpreter": {
    "hash": "cf2a50979671a58939829e6829efb726aa5da11149213b77bd50351f899d04fb"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
